﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;

namespace RLZY.others
{
    class myGZ
    {
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListPart(int Top, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" [ID],[日期],[部门单位],[车间班组],[姓名],[日班],[中班],[晚班],[大晚班],[中晚班费],[加班],[加班工资],[补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假],[工伤], [产假],[其它扣款],津贴4,津贴5 as 餐贴,扣款5 as 扣餐贴  ");
            strSql.Append(" FROM GZ ");
            strSql.Append("  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') and 是否保护='否' ");
            strSql.Append(" order   by  姓名 asc ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",strWhere),
                

            };
            
            return DbHelperSQL.Query(strSql.ToString(), param);
           
        }
        /// <summary>
        /// 获得班组数据列表
        /// </summary>
        public DataSet GetListBZ(String qx)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct 车间班组 from GZ  where 部门单位  like '%'+'" + qx + "'+'%' and 是否保护='否' ");
            
           
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 根据权限获取数据
        /// </summary>
        public DataSet GetListBM(string bm)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct 车间班组 from GZ  ");
            strSql.Append("  where 部门单位  like   '%'+ @部门单位 +'%'    ");
            strSql.Append("and 是否保护='否' order by 车间班组 ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                new SqlParameter("@部门单位",bm),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 根据部门获得数据列表
        /// </summary>
        public DataSet GetListPartBZ(int Top, string strWhere,String strWhere1)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" [ID],[日期],[部门单位],[车间班组],[姓名],[日班],[中班],[晚班],[大晚班],[中晚班费],[加班],[加班工资],[补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假],[工伤], [产假],[其它扣款],津贴4,津贴5 as 餐贴,扣款5 as 扣餐贴  ");
            strSql.Append(" FROM GZ ");
            strSql.Append("  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') and (车间班组 like '%'+@strWhere1+'%' or @strWhere1='') and  是否保护='否' ");
            strSql.Append(" order   by  姓名 asc ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",strWhere),
                new SqlParameter("@strWhere1",strWhere1),
                

            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 获取最后保护日期
        /// </summary>
        public DataSet GetListLastDate(string bm)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   top 1 日期,是否保护 from GZ ");
            strSql.Append(" where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') ");
            strSql.Append(" and 是否保护='是' order by 日期 desc ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                new SqlParameter("@strWhere",bm),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 获取最后日期
        /// </summary>
        public DataSet GetLastDate()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   top 1 日期 from GZ order by 日期 desc ");
            
            return DbHelperSQL.Query(strSql.ToString(), null);
        }
        /// <summary>
        /// 获取最后非保护日期
        /// </summary>
        public DataSet GetListLastDateNOtLimited(string bm)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   top 1 日期,是否保护 from GZ ");
            strSql.Append(" where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') ");
            strSql.Append(" and 是否保护='否' order by 日期 desc ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                new SqlParameter("@strWhere",bm),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 初始化NULL值为0
        /// </summary>
        public int  set0TONull()
        {
            List<String> list = new List<string>();
            list.Add("update GZ set [技能工资]=0 where [技能工资] is Null  ");
            list.Add("update GZ set [岗位工资]=0 where [岗位工资] is Null  ");
            list.Add("update GZ set [绩效工资]=0 where [绩效工资] is Null  ");
            list.Add("update GZ set [粮贴]=0 where [粮贴] is Null  ");
            list.Add("update GZ set [洗理费]=0 where [洗理费] is Null  ");
            list.Add("update GZ set [水电气补贴]=0 where [水电气补贴] is Null  ");
            list.Add("update GZ set [三项补贴]=0 where [三项补贴] is Null  ");
            list.Add("update GZ set [回贴]=0 where [回贴] is Null  ");
            list.Add("update GZ set [工龄津贴]=0 where [工龄津贴] is Null  ");
            list.Add("update GZ set [职称津贴]=0 where [职称津贴] is Null  ");
            list.Add("update GZ set [特岗津贴]=0 where [特岗津贴] is Null  ");
            list.Add("update GZ set [行车工津贴]=0 where [行车工津贴] is Null  ");
            list.Add("update GZ set [卫生纸费]=0 where [卫生纸费] is Null  ");
            list.Add("update GZ set [日班]=0 where [日班] is Null  ");
            list.Add("update GZ set [中班]=0 where [中班] is Null  ");
            list.Add("update GZ set [晚班]=0 where [晚班] is Null  ");
            list.Add("update GZ set [大晚班]=0 where [大晚班] is Null  ");
            list.Add("update GZ set [中晚班费]=0 where [中晚班费] is Null  ");
            list.Add("update GZ set [加班]=0 where [加班] is Null  ");
            list.Add("update GZ set [加班工资]=0 where [加班工资] is Null  ");
            list.Add("update GZ set [补发]=0 where [补发] is Null  ");
            list.Add("update GZ set [事假]=0 where [事假] is Null  ");
            list.Add("update GZ set [扣事假工资]=0 where [扣事假工资] is Null  ");
            list.Add("update GZ set [病假]=0 where [病假] is Null  ");
            list.Add("update GZ set [扣病假工资]=0 where [扣病假工资] is Null  ");
            list.Add("update GZ set [公假]=0 where [公假] is Null  ");
            list.Add("update GZ set [工伤]=0 where [工伤] is Null  ");
            list.Add("update GZ set [产假]=0 where [产假] is Null  ");
            list.Add("update GZ set [其它扣款]=0 where [其它扣款] is Null  ");
            list.Add("update GZ set [应发总额]=0 where [应发总额] is Null  ");
            list.Add("update GZ set [扣失业保险]=0 where [扣失业保险] is Null  ");
            list.Add("update GZ set [扣养老金]=0 where [扣养老金] is Null  ");
            list.Add("update GZ set [扣医疗保险]=0 where [扣医疗保险] is Null  ");
            list.Add("update GZ set [扣公积金]=0 where [扣公积金] is Null  ");
            list.Add("update GZ set [扣电视费]=0 where [扣电视费] is Null  ");
            list.Add("update GZ set [扣治安费]=0 where [扣治安费] is Null  ");
            list.Add("update GZ set [扣会费]=0 where [扣会费] is Null  ");
            list.Add("update GZ set [扣互助金]=0 where [扣互助金] is Null  ");
            list.Add("update GZ set [扣丧葬费]=0 where [扣丧葬费] is Null  ");
            list.Add("update GZ set [实发金额]=0 where [实发金额] is Null  ");
            list.Add("update GZ set [车贴]=0 where [车贴] is Null  ");
            list.Add("update GZ set [星级津贴]=0 where [星级津贴] is Null  ");
            list.Add("update GZ set [津贴1]=0 where [津贴1] is Null  ");
            list.Add("update GZ set [津贴2]=0 where [津贴2] is Null  ");
            list.Add("update GZ set [津贴3]=0 where [津贴3] is Null  ");
            list.Add("update GZ set [津贴4]=0 where [津贴4] is Null  ");
            list.Add("update GZ set [津贴5]=0 where [津贴5] is Null  ");
            list.Add("update GZ set [五项津贴]=0 where [五项津贴] is Null  ");
            list.Add("update GZ set [应发]=0 where [应发] is Null  ");
            list.Add("update GZ set [扣款1]=0 where [扣款1] is Null  ");
            list.Add("update GZ set [扣款2]=0 where [扣款2] is Null  ");
            list.Add("update GZ set [扣款3]=0 where [扣款3] is Null  ");
            list.Add("update GZ set [扣款4]=0 where [扣款4] is Null  ");
            list.Add("update GZ set [扣款5]=0 where [扣款5] is Null  ");
            list.Add("update GZ set [五项扣款]=0 where [五项扣款] is Null  ");
            list.Add("update GZ set [实发]=0 where [实发] is Null  ");
            

            return DbHelperSQL.ExecuteSqlTran(list);
        }
        /// <summary>
        /// 日期段考勤
        /// </summary>
        /// <summary>
        /// 日期段考勤
        /// </summary>
        public DataSet GetDateKQ( string QX, String BZ,DateTime start,DateTime end)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");

            strSql.Append(" [ID], [日期], [部门单位], [车间班组],  [姓名],  [日班], [中班], [晚班], [大晚班], [中晚班费], [加班], [加班工资], [补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假], [工伤], [产假], [其它扣款] ");
            strSql.Append(" FROM GZ ");
            strSql.Append("  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') and (车间班组 like '%'+@strWhere1+'%' or @strWhere1='')");
            strSql.Append("  and 日期>=@起始日期 ");

            strSql.Append("  and 日期<=@截止日期 ");
            strSql.Append("  order   by  姓名 asc ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",QX),
                new SqlParameter("@strWhere1",BZ),
                new SqlParameter("@起始日期",start),
                new SqlParameter("@截止日期",end),
            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 日期段工资
        /// </summary>
        public DataSet GetDateGZ(string QX, String BZ, DateTime start, DateTime end)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select *");
            strSql.Append(" FROM GZ ");
            strSql.Append("  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') and (车间班组 like '%'+@strWhere1+'%' or @strWhere1='')");
            strSql.Append("  and 日期>=@起始日期 ");
            strSql.Append("  and 日期<=@截止日期 ");
            strSql.Append("  order   by  ID desc ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",QX),
                new SqlParameter("@strWhere1",BZ),
                new SqlParameter("@起始日期",start),
                new SqlParameter("@截止日期",end),
            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 日期段工资按照用工性质
        /// </summary>
        public DataSet GetDateGZAsWork(string QX, String BZ, DateTime start, DateTime end)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" [用工性质],sum(人数) as 人数, sum(技能工资) as 技能工资, sum(岗位工资) as 岗位工资, Sum(绩效工资) as 绩效工资, sum(粮贴) as 粮贴, sum(洗理费) as 洗理费, Sum(水电气补贴) as 水电气补贴, sum(三项补贴) as 三项补贴, sum(回贴) as 回贴, sum(工龄津贴) as 工龄津贴, sum(职称津贴) as 职称津贴, sum(特岗津贴) as 特岗津贴, sum(行车工津贴) as 行车工津贴, sum(卫生纸费) as 卫生纸费, sum(日班) as 日班, sum(中班) as 中班, sum(晚班) as 晚班, sum(大晚班) as 大晚班, sum(中晚班费) as 中晚班费, sum(加班) as 加班, sum(加班工资) as 加班工资, sum(补发) as 补发, sum(事假) as 事假, sum(扣事假工资) as 扣事假工资, sum(病假) as 病假, sum(扣病假工资) as 扣病假工资, sum(公假) as 公假, sum(工伤) as 工伤, sum(产假) as 产假, sum(其它扣款) as 其它扣款, sum(应发总额) as 应发总额, sum(扣失业保险) as 扣失业保险, sum(扣养老金) as 扣养老金, sum(扣医疗保险) as 扣医疗保险, sum(扣公积金) as 扣公积金, sum(扣电视费) as 扣电视费, sum(扣治安费) as 扣治安费, sum(扣会费) as 扣会费, sum(扣互助金) as 扣互助金, sum(扣丧葬费) as 扣丧葬费, sum(实发金额) as  实发金额 ");
            strSql.Append(" FROM GZ ");
            strSql.Append("  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') and (车间班组 like '%'+@strWhere1+'%' or @strWhere1='')");
            strSql.Append("  and 日期>=@起始日期 ");
            strSql.Append("  and 日期<=@截止日期 ");
            strSql.Append("  group by 用工性质 with CUBE ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",QX),
                new SqlParameter("@strWhere1",BZ),
                new SqlParameter("@起始日期",start),
                new SqlParameter("@截止日期",end),
            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 日期段工资按照班组
        /// </summary>
        public DataSet GetDateGZAsBZ(string QX, String BZ, DateTime start, DateTime end)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" 车间班组,sum(人数) as 人数, sum(技能工资) as 技能工资, sum(岗位工资) as 岗位工资, Sum(绩效工资) as 绩效工资, sum(粮贴) as 粮贴, sum(洗理费) as 洗理费, Sum(水电气补贴) as 水电气补贴, sum(三项补贴) as 三项补贴, sum(回贴) as 回贴, sum(工龄津贴) as 工龄津贴, sum(职称津贴) as 职称津贴, sum(特岗津贴) as 特岗津贴, sum(行车工津贴) as 行车工津贴, sum(卫生纸费) as 卫生纸费, sum(日班) as 日班, sum(中班) as 中班, sum(晚班) as 晚班, sum(大晚班) as 大晚班, sum(中晚班费) as 中晚班费, sum(加班) as 加班, sum(加班工资) as 加班工资, sum(补发) as 补发, sum(事假) as 事假, sum(扣事假工资) as 扣事假工资, sum(病假) as 病假, sum(扣病假工资) as 扣病假工资, sum(公假) as 公假, sum(工伤) as 工伤, sum(产假) as 产假, sum(其它扣款) as 其它扣款, sum(应发总额) as 应发总额, sum(扣失业保险) as 扣失业保险, sum(扣养老金) as 扣养老金, sum(扣医疗保险) as 扣医疗保险, sum(扣公积金) as 扣公积金, sum(扣电视费) as 扣电视费, sum(扣治安费) as 扣治安费, sum(扣会费) as 扣会费, sum(扣互助金) as 扣互助金, sum(扣丧葬费) as 扣丧葬费, sum(实发金额) as  实发金额 ");
            strSql.Append(" FROM GZ ");
            strSql.Append("  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') and (车间班组 like '%'+@strWhere1+'%' or @strWhere1='')");
            strSql.Append("  and 日期>=@起始日期 ");
            strSql.Append("  and 日期<=@截止日期 ");
            strSql.Append("  group by 车间班组 with CUBE ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",QX),
                new SqlParameter("@strWhere1",BZ),
                new SqlParameter("@起始日期",start),
                new SqlParameter("@截止日期",end),
            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 单人查询
        /// </summary>
        public DataSet GetGZByName(string QX, String BZ, String name, String nameid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select *");
            strSql.Append(" FROM GZ ");
            strSql.Append("  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') and (车间班组 like '%'+@strWhere1+'%' or @strWhere1='')");
            strSql.Append("  and (姓名 like '%'+@strWhere3+'%' or @strWhere3='') and (身份证号 like '%'+@strWhere4+'%' or @strWhere4='') ");
            strSql.Append("  order   by  ID desc ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",QX),
                new SqlParameter("@strWhere1",BZ),
                new SqlParameter("@strWhere3",name),
                new SqlParameter("@strWhere4",nameid),
            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 未考勤人员
        /// </summary>
        public DataSet GetGZNOKQ(string QX)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" [ID], [日期], [部门单位], [车间班组],  [姓名],  [日班], [中班], [晚班], [大晚班], [中晚班费], [加班], [加班工资], [补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假], [工伤], [产假], [其它扣款]  ");
            strSql.Append(" FROM GZ ");
            strSql.Append("  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='')");
            strSql.Append("  and   是否保护='否'  and  应发总额=0  and not 部门单位='锻造厂' order by 姓名 asc ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",QX),
            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 初始化
        /// </summary>
        public int set0TONull(string QX )
        {
            List<String> list = new List<string>();
            list.Add(" if  exists (select name from sysobjects where name='GZTmp')  drop table GZTmp ");
            list.Add(" select  [日期], [部门单位], [车间班组], [姓名], [性别], [技能工资], [岗位工资], [绩效工资], [粮贴], [洗理费], [水电气补贴], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [行车工津贴], [卫生纸费], [车贴], [星级津贴], [津贴1], [津贴2], [津贴3], [津贴4], [津贴5], [五项津贴], [日班], [中班], [晚班], [大晚班], [中晚班费], [加班], [加班工资], [补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假], [工伤], [产假], [其它扣款], [应发总额], [应发], [扣失业保险], [扣养老金], [扣医疗保险], [扣公积金], [扣电视费], [扣治安费], [扣会费], [扣互助金], [扣丧葬费], [扣款1], [扣款2], [扣款3], [扣款4], [扣款5], [五项扣款], [实发金额], [实发], [帐号], [技能工资级别], [岗位工资类别], [岗位名称], [参加工作时间], [身份证号], [用工性质], [备注], [部门单位备注], [工号], [老工号], [公积金号], [医疗保险号], [出生日期], [党政职别], [职称], [干工标识], [籍贯_出生地], [毕业时间文化程度], [人数], [是否保护], [行车工津贴2],星级津贴2, [tmp] into GZTmp from GZ  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') and substring(日期,1,7)=@date   and 是否保护='否' ");
            list.Add(" update GZTmp set [日班]=0   ");
            list.Add("update GZTmp set [中班]=0   ");
            list.Add("update GZTmp set [晚班]=0  ");
            list.Add("update GZTmp set [大晚班]=0  ");
            list.Add("update GZTmp set [中晚班费]=0   ");
            list.Add("update GZTmp set [加班]=0   ");
            list.Add("update GZTmp set [加班工资]=0  ");
            list.Add("update GZTmp set [补发]=0  ");
            list.Add("update GZTmp set [事假]=0  ");
            list.Add("update GZTmp set [扣事假工资]=0   ");
            list.Add("update GZTmp set [病假]=0   ");
            list.Add("update GZTmp set [扣病假工资]=0   ");
            list.Add("update GZTmp set [公假]=0  ");
            list.Add("update GZTmp set [工伤]=0   ");
            list.Add("update GZTmp set [产假]=0  ");
            list.Add("update GZTmp set [其它扣款]=0  ");
            list.Add("update GZTmp set [应发总额]=0  ");
            list.Add("update GZTmp set [实发金额]=0  ");
            list.Add("update GZTmp set [应发]=0  ");
            list.Add("update GZTmp set [实发]=0   ");
            list.Add("update GZTmp set [人数]=1  ");
            list.Add("update GZTmp set 是否保护='否'  ");
            list.Add("update GZTmp set 日期=@日期 ");
            list.Add("update GZTmp set 行车工津贴=行车工津贴2  ");
            list.Add("update GZTmp set 星级津贴=星级津贴2  ");
            list.Add("update GZTmp set [扣款4]=0  ");
            list.Add("update GZTmp set [扣款5]=0  ");
            list.Add("insert into  GZ([日期], [部门单位], [车间班组], [姓名], [性别], [技能工资], [岗位工资], [绩效工资], [粮贴], [洗理费], [水电气补贴], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [行车工津贴], [卫生纸费], [车贴], [星级津贴], [津贴1], [津贴2], [津贴3], [津贴4], [津贴5], [五项津贴], [日班], [中班], [晚班], [大晚班], [中晚班费], [加班], [加班工资], [补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假], [工伤], [产假], [其它扣款], [应发总额], [应发], [扣失业保险], [扣养老金], [扣医疗保险], [扣公积金], [扣电视费], [扣治安费], [扣会费], [扣互助金], [扣丧葬费], [扣款1], [扣款2], [扣款3], [扣款4], [扣款5], [五项扣款], [实发金额], [实发], [帐号], [技能工资级别], [岗位工资类别], [岗位名称], [参加工作时间], [身份证号], [用工性质], [备注], [部门单位备注], [工号], [老工号], [公积金号], [医疗保险号], [出生日期], [党政职别], [职称], [干工标识], [籍贯_出生地], [毕业时间文化程度], [人数], [是否保护], [行车工津贴2],星级津贴2, [tmp])  select  [日期], [部门单位], [车间班组], [姓名], [性别], [技能工资], [岗位工资], [绩效工资], [粮贴], [洗理费], [水电气补贴], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [行车工津贴], [卫生纸费], [车贴], [星级津贴], [津贴1], [津贴2], [津贴3], [津贴4], [津贴5], [五项津贴], [日班], [中班], [晚班], [大晚班], [中晚班费], [加班], [加班工资], [补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假], [工伤], [产假], [其它扣款], [应发总额], [应发], [扣失业保险], [扣养老金], [扣医疗保险], [扣公积金], [扣电视费], [扣治安费], [扣会费], [扣互助金], [扣丧葬费], [扣款1], [扣款2], [扣款3], [扣款4], [扣款5], [五项扣款], [实发金额], [实发], [帐号], [技能工资级别], [岗位工资类别], [岗位名称], [参加工作时间], [身份证号], [用工性质], [备注], [部门单位备注], [工号], [老工号], [公积金号], [医疗保险号], [出生日期], [党政职别], [职称], [干工标识], [籍贯_出生地], [毕业时间文化程度], [人数], [是否保护], [行车工津贴2],星级津贴2, [tmp] from  GZTmp       ");
            list.Add(" if  exists (select name from sysobjects where name='GZTmp2')  drop table GZTmp2  ");
            list.Add("select     [姓名],  [公积金号], 用工性质, [部门单位], 扣公积金*2 as 公积金入帐 ,[日期]  into GZTmp2  from GZ   where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') and substring(日期,1,7)=@date   and 是否保护='否' and not(公积金号 is null or 公积金号=' ') ");
            list.Add("  insert into  GJJ_RZ  select  [姓名], [公积金号], [用工性质], [部门单位], [公积金入帐], [日期] from  GZTmp2       ");
            list.Add(" update GZ   set  是否保护='是'  where  substring(日期,1,7)<=@date ");
            list.Add("if  exists (select name from sysobjects where name='GZTmp')  drop table GZTmp  ");
            list.Add("if  exists (select name from sysobjects where name='GZTmp2')  drop table GZTmp2  ");
            list.Add("if  exists (select name from sysobjects where name='GZTmp3')  drop table GZTmp3  ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",QX),


            };


            return DbHelperSQL.ExecuteSqlTran(list);
        }
        /// <summary>
        /// 特钢本部日期段工资按照用工性质
        /// </summary>
        public DataSet GetDateGZTG( DateTime start, DateTime end)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" [用工性质],sum(人数) as 人数, sum(技能工资) as 技能工资, sum(岗位工资) as 岗位工资, Sum(绩效工资) as 绩效工资, sum(粮贴) as 粮贴, sum(洗理费) as 洗理费, Sum(水电气补贴) as 水电气补贴, sum(三项补贴) as 三项补贴, sum(回贴) as 回贴, sum(工龄津贴) as 工龄津贴, sum(职称津贴) as 职称津贴, sum(特岗津贴) as 特岗津贴, sum(行车工津贴) as 行车工津贴, sum(卫生纸费) as 卫生纸费, sum(日班) as 日班, sum(中班) as 中班, sum(晚班) as 晚班, sum(大晚班) as 大晚班, sum(中晚班费) as 中晚班费, sum(加班) as 加班, sum(加班工资) as 加班工资, sum(补发) as 补发, sum(事假) as 事假, sum(扣事假工资) as 扣事假工资, sum(病假) as 病假, sum(扣病假工资) as 扣病假工资, sum(公假) as 公假, sum(工伤) as 工伤, sum(产假) as 产假, sum(其它扣款) as 其它扣款, sum(应发总额) as 应发总额, sum(扣失业保险) as 扣失业保险, sum(扣养老金) as 扣养老金, sum(扣医疗保险) as 扣医疗保险, sum(扣公积金) as 扣公积金, sum(扣电视费) as 扣电视费, sum(扣治安费) as 扣治安费, sum(扣会费) as 扣会费, sum(扣互助金) as 扣互助金, sum(扣丧葬费) as 扣丧葬费, sum(实发金额) as  实发金额 ");
            strSql.Append(" FROM GZ ");
            strSql.Append("  where 日期>=@起始日期 ");
            strSql.Append("  and 日期<=@截止日期 ");
            strSql.Append("  and (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')   group by 用工性质 with CUBE  ");

            SqlParameter[] param = new SqlParameter[] 
            {
                new SqlParameter("@起始日期",start),
                new SqlParameter("@截止日期",end),
            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        ///除锻造厂工资按照用工性质
        /// </summary>
        public DataSet GetDateGZEXCEPT(DateTime start, DateTime end)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" [用工性质],sum(人数) as 人数, sum(技能工资) as 技能工资, sum(岗位工资) as 岗位工资, Sum(绩效工资) as 绩效工资, sum(粮贴) as 粮贴, sum(洗理费) as 洗理费, Sum(水电气补贴) as 水电气补贴, sum(三项补贴) as 三项补贴, sum(回贴) as 回贴, sum(工龄津贴) as 工龄津贴, sum(职称津贴) as 职称津贴, sum(特岗津贴) as 特岗津贴, sum(行车工津贴) as 行车工津贴, sum(卫生纸费) as 卫生纸费, sum(日班) as 日班, sum(中班) as 中班, sum(晚班) as 晚班, sum(大晚班) as 大晚班, sum(中晚班费) as 中晚班费, sum(加班) as 加班, sum(加班工资) as 加班工资, sum(补发) as 补发, sum(事假) as 事假, sum(扣事假工资) as 扣事假工资, sum(病假) as 病假, sum(扣病假工资) as 扣病假工资, sum(公假) as 公假, sum(工伤) as 工伤, sum(产假) as 产假, sum(其它扣款) as 其它扣款, sum(应发总额) as 应发总额, sum(扣失业保险) as 扣失业保险, sum(扣养老金) as 扣养老金, sum(扣医疗保险) as 扣医疗保险, sum(扣公积金) as 扣公积金, sum(扣电视费) as 扣电视费, sum(扣治安费) as 扣治安费, sum(扣会费) as 扣会费, sum(扣互助金) as 扣互助金, sum(扣丧葬费) as 扣丧葬费, sum(实发金额) as  实发金额 ");
            strSql.Append(" FROM GZ ");
            strSql.Append("  where 日期>=@起始日期 ");
            strSql.Append("  and 日期<=@截止日期 ");
            strSql.Append("   and not(部门单位='锻造厂')   group by 用工性质 with CUBE   ");

            SqlParameter[] param = new SqlParameter[] 
            {
                new SqlParameter("@起始日期",start),
                new SqlParameter("@截止日期",end),
            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 初始化
        /// </summary>
        public int setAll()
        {
            List<String> list = new List<string>();

            list.Add(" update GZ set 应发总额 =技能工资+岗位工资+绩效工资+粮贴+回贴+洗理费+水电气补贴+工龄津贴+职称津贴+特岗津贴+行车工津贴+卫生纸费+车贴+星级津贴+津贴1+津贴2+津贴3+津贴4+津贴5+补发+中晚班费+加班工资-扣病假工资-扣事假工资-其它扣款   where 部门单位='工贸'  and 日期='2014-05-02'and 是否保护='否'   ");
            list.Add(" update GZ set 实发金额=应发总额-扣会费-扣丧葬费-扣互助金-扣治安费-扣电视费-扣养老金-扣失业保险-扣医疗保险-扣公积金-扣款1-扣款2-扣款3-扣款4-扣款5    where 部门单位='工贸'  and 日期='2014-05-02'and 是否保护='否'  ");
            list.Add(" update GZ set 应发 =技能工资+岗位工资+绩效工资+粮贴+回贴+洗理费+水电气补贴+工龄津贴+职称津贴+特岗津贴+卫生纸费+补发+中晚班费+加班工资-扣病假工资-扣事假工资-其它扣款  where 部门单位='工贸'  and 日期='2014-05-02'and 是否保护='否' ");
            list.Add(" update GZ set 实发=应发-扣会费-扣丧葬费-扣互助金-扣治安费-扣电视费-扣养老金-扣失业保险-扣医疗保险-扣公积金-扣款5   where 部门单位='工贸'  and 日期='2014-05-02'and 是否保护='否'  ");
            
            return DbHelperSQL.ExecuteSqlTran(list);
        }
    }
}
